package backend.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Iterator;
import java.util.LinkedList;

import logs.LogFactory;

import org.apache.log4j.Logger;

import backend.ConnectionPool;
import backend.Course;
import backend.Day;
import backend.Session;
import backend.User;
import backend.exceptions.ElementNotFoundException;

public class ORM {
  private static final Logger logger = LogFactory.getLog(ORM.class);
  public static ConnectionPool cp = new ConnectionPool();
  
  public static User getUser(final String username) {
    if (null == username)
      return null;
    User $ = null;
    final ConnectionManager cm = cp.captureConnection();
    try {
      $ = DBHandler.selectElement(cm.getConn(), "users",
          DBHandler.singleParam(new Cell(Tables.getTable("users").getColumn("username"), username)),
          Makers.USER_MAKER);
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return $;
  }
  
  public static void addUser(final String username, final String password, final String role,
      final String name, final String phone, final String address) {
    final ConnectionManager cm = cp.captureConnection();
    try {
      DBHandler.insert(cm.getConn(), "users",
          Tables.getTable("users").makeCellArray(username, password, role, name, phone, address));
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  // not atomic action (has also addSessions, addSpace)
  public static void addCourse(final Connection c, final String username, final String courseId,
      final String courseName, final String lecturerInCharge, final String moedA, final String moedB,
      final String description, final String creditPoints, final String capacity, final String groupNum) {
    DBHandler.insert(
        c,
        "courses",
        Tables.getTable("courses").makeCellArray(courseName, courseId, lecturerInCharge, moedA, moedB,
            description, creditPoints, Integer.valueOf(capacity), Integer.valueOf(groupNum), username));
  }
  
  // not atomic action (has also addSessions, addCourse)
  public static void addCourseSpace(final Connection c, final String courseName, final int capacity) {
    DBHandler.insert(c, "capacities",
        Tables.getTable("capacities").makeCellArray(courseName, Integer.valueOf(capacity)));
  }
  
  // not atomic action (has also addSpace, addCourse)
  public static void addCourseSession(final Connection c, final String courseId, final String day,
      final String startHour, final String duration, final String teacherName, final String classLocation,
      final String type) {
    boolean isLecture;
    if ("lecture".equals(type))
      isLecture = true;
    else if ("tutorial".equals(type))
      isLecture = false;
    else
      throw new IllegalArgumentException("'type' parameter is neither \"lecture\" nor \"tutorial\".");
    DBHandler.insert(
        c,
        "sessions",
        Tables.getTable("sessions").makeCellArray(courseId, Integer.valueOf(Day.valueOf(day).ordinal()),
            Integer.valueOf(startHour), Integer.valueOf(duration), teacherName, classLocation,
            Boolean.valueOf(isLecture)));
  }
  
  public static Iterable<Course> getAllCourses() {
    Collection<Course> $ = null;
    final ConnectionManager cm = cp.captureConnection();
    try {
      $ = DBHandler.selectSome(cm.getConn(), "courses", DBHandler.NO_PARAMS, Makers.DEEP_COURSE_MAKER);
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return $;
  }
  
  public static void removeStudent(final String username) {
    final ConnectionManager cm = cp.captureConnection();
    try {
      final Iterable<Course> registeredCourses = ORM.getMyCourses(username);
      for (final Course c : registeredCourses)
        updateCourseSpace(cm.getConn(), c.courseName, 1);
      DBHandler.delete(cm.getConn(), "registrations",
          DBHandler.singleParam(new Cell(Tables.getTable("registrations").getColumn("username"), username)));
      DBHandler.delete(cm.getConn(), "users",
          DBHandler.singleParam(new Cell(Tables.getTable("users").getColumn("username"), username)));
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static void removeCourse(final String courseName) {
    final ConnectionManager cm = cp.captureConnection();
    try {
      final String courseId = courseNameToId(cm.getConn(), courseName);
      DBHandler.delete(cm.getConn(), "sessions",
          DBHandler.singleParam(new Cell(Tables.getTable("sessions").getColumn("courseId"), courseId)));
      DBHandler.delete(cm.getConn(), "registrations", DBHandler.singleParam(new Cell(Tables.getTable(
          "registrations").getColumn("courseName"), courseName)));
      DBHandler.delete(cm.getConn(), "capacities",
          DBHandler.singleParam(new Cell(Tables.getTable("capacities").getColumn("courseName"), courseName)));
      DBHandler.delete(cm.getConn(), "courses",
          DBHandler.singleParam(new Cell(Tables.getTable("courses").getColumn("courseName"), courseName)));
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  // not atomic - no need for rollback/commit
  static String courseNameToId(final Connection c, final String courseName) throws ElementNotFoundException {
    return DBHandler.selectElement(c, "courseId", "courses",
        DBHandler.singleParam(new Cell(Tables.getTable("courses").getColumn("courseName"), courseName)),
        new Maker<String>() {
          @Override public String make(final ResultSet r) throws SQLException {
            return r.getString("courseId");
          }
        });
  }
  
  public static boolean isCourseAvailable(final String username, final Course c) {
    Collection<String> myCoursesNames;
    Collection<Integer> myGroups;
    Iterable<Session> nominatedSessions;
    Iterable<Session> mySessions;
    String[] nominatedCourseCapacity;
    final ConnectionManager cm = cp.captureConnection();
    try {
      try {
        myCoursesNames = ORM.getMyCoursesNames(cm.getConn(), username);
        myGroups = getUserStudyGroups(cm.getConn(), username);
        nominatedSessions = getCourseSessions(c.courseId);
        final Iterable<Course> myCourses = ORM.getMyCourses(username);
        mySessions = getAllUserCoursesSessions(myCourses);
        final Cell[] params = DBHandler.singleParam(new Cell(Tables.getTable("capacities").getColumn(
            "courseName"), c.courseName));
        nominatedCourseCapacity = DBHandler.selectElement(cm.getConn(), "courseName, space", "capacities",
            params, new Maker<String[]>() {
              @Override public String[] make(final ResultSet r) throws SQLException {
                return new String[] { r.getString("courseName"), r.getString("space") };
              }
            });
        cm.getConn().commit();
      } catch (final Exception e) {
        try {
          cm.getConn().rollback();
        } catch (final SQLException e1) {
          logger.error("rollback failed", e1);
        }
        return false;
      }
      if (Integer.parseInt(nominatedCourseCapacity[1]) == 0) {
        logger.debug("AC=" + c.courseName + ": space=0");
        return false;
      }
      if (ORM.overlaps(nominatedSessions, mySessions)) {
        logger.debug("AC=" + c.courseName + ": course overlaps current registered sessions");
        return false;
      }
      if (!myGroups.contains(Integer.valueOf(c.groupNum)))
        if (myGroups.size() == 3) {
          logger.debug("AC=" + c.courseName + ": can't have more than 3 study groups");
          return false;
        }
      if (myCoursesNames.contains(c.courseName)) {
        logger.debug("AC=" + c.courseName + ": user is already registered to course");
        return false;
      }
      logger.debug("AC=" + c.courseName + ": AVAILABLE");
      return true;
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static Iterable<Course> getAvailableCourses(final String username) {
    Iterable<Course> $ = null;
    final ConnectionManager cm = cp.captureConnection();
    try {
      $ = DBHandler.selectAvailable(cm.getConn(), username);
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return $;
  }
  
  // TODO: remove once new version is checked:
  public static Iterable<Course> getAvailableCourses_take1(final String username) {
    Iterable<Course> allCourses = null;
    final ConnectionManager cm = cp.captureConnection();
    try {
      final Iterable<Course> myCourses = ORM.getMyCourses(username);
      allCourses = ORM.getAllCourses();
      final Iterable<Session> mySessions = getAllUserCoursesSessions(myCourses);
      final Collection<String> myCoursesNames = ORM.getMyCoursesNames(cm.getConn(), username);
      final Collection<Integer> myGroups = getUserStudyGroups(cm.getConn(), username);
      final Iterable<String[]> availableCourses = DBHandler.selectSome(cm.getConn(), "courseName, space",
          "capacities", DBHandler.NO_PARAMS, new Maker<String[]>() {
            @Override public String[] make(final ResultSet r) throws SQLException {
              return new String[] { r.getString("courseName"), r.getString("space") };
            }
          });
      for (final String[] courseAndSpace : availableCourses) {
        final Course nominatedCourse = ORM.getCourseByName(courseAndSpace[0]);
        final Collection<Session> nominatedSessions = getCourseSessions(nominatedCourse.courseId);
        if (Integer.parseInt(courseAndSpace[1]) == 0) {
          // capacity - 1'st condition
          ORM.removeCourseFromGroup(nominatedCourse.courseName, allCourses);
          logger.debug("1)removed " + nominatedCourse.courseName);
        } else if (overlaps(nominatedSessions, mySessions)) {
          // session overlapping - 2'nd condition
          ORM.removeCourseFromGroup(nominatedCourse.courseName, allCourses);
          logger.debug("2)removed " + nominatedCourse.courseName);
        } else if (!myGroups.contains(Integer.valueOf(nominatedCourse.groupNum)))
          if (myGroups.size() == 3) // more than 3 groups - 3'rd condition
          {
            ORM.removeCourseFromGroup(nominatedCourse.courseName, allCourses);
            logger.debug("3)removed " + nominatedCourse.courseName);
          } else if (myCoursesNames.contains(nominatedCourse.courseName)) {
            ORM.removeCourseFromGroup(nominatedCourse.courseName, allCourses);
            // already registered - 4'th condition
            logger.debug("4)removed " + nominatedCourse.courseName);
          }
      }
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return allCourses;
  }
  
  private static void removeCourseFromGroup(final String courseName, final Iterable<Course> allCourses) {
    final Iterator<Course> iter = allCourses.iterator();
    while (iter.hasNext())
      if (courseName.equals(iter.next().courseName)) {
        iter.remove();
        return;
      }
  }
  
  static boolean overlaps(final Iterable<Session> nominatedSessions, final Iterable<Session> sessions) {
    for (final Session s1 : nominatedSessions) {
      final int sh1 = s1.startHour;
      final int eh1 = sh1 + s1.duration;
      for (final Session s2 : sessions) {
        final int sh2 = s2.startHour;
        final int eh2 = sh2 + s2.duration;
        if (s1.day == s2.day && !(eh1 <= sh2 || sh1 >= eh2))
          return true;
      }
    }
    return false;
  }
  
  public static Iterable<Course> getAllCoursesShallow() {
    final ConnectionManager cm = cp.captureConnection();
    try {
      return DBHandler.selectSome(cm.getConn(), "courses", DBHandler.NO_PARAMS, Makers.SHALLOW_COURSE_MAKER);
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static Course getCourseByName(final String courseName) throws ElementNotFoundException {
    return getCourse("courseName", courseName);
  }
  
  public static Course getCourseById(final String courseId) throws ElementNotFoundException {
    return getCourse("courseId", courseId);
  }
  
  private static Course getCourse(final String colName, final String val) throws ElementNotFoundException {
    final ConnectionManager cm = cp.captureConnection();
    try {
      return DBHandler.selectElement(cm.getConn(), "courses",
          DBHandler.singleParam(new Cell(Tables.getTable("courses").getColumn(colName), val)),
          Makers.DEEP_COURSE_MAKER);
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static boolean isUserRegisteredToCourse(final String username, final Course course) {
    boolean $ = true;
    final Table registrations = Tables.getTable("registrations");
    final ConnectionManager cm = cp.captureConnection();
    try {
      DBHandler.selectElement(
          cm.getConn(),
          "0",
          "registrations",
          new Cell[] { new Cell(registrations.getColumn("username"), username),
              new Cell(registrations.getColumn("courseName"), course.courseName) }, new Maker<Object>() {
            @Override public Object make(final ResultSet result) {
              return null;
            }
          });
      cm.getConn().commit();
    } catch (final ElementNotFoundException e) {
      $ = false;
    } catch (final SQLException e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return $;
  }
  
  public static Iterable<Course> getMyCourses(final String username) {
    return getMyCoursesHelper(username, Makers.DEEP_COURSE_MAKER);
  }
  
  public static Iterable<Course> getMyCoursesShallow(final String username) {
    return getMyCoursesHelper(username, Makers.SHALLOW_COURSE_MAKER);
  }
  
  private static Iterable<Course> getMyCoursesHelper(final String username, final Maker<Course> courseMaker) {
    final Collection<Course> $ = new LinkedList<Course>();
    final Cell[] cells = DBHandler.singleParam(new Cell(Tables.getTable("registrations")
        .getColumn("username"), username));
    final Cell[] cells2 = DBHandler.singleParam(null);
    final ConnectionManager cm = cp.captureConnection();
    try {
      final Iterable<String> res = DBHandler.selectSome(cm.getConn(), "courseName", "registrations", cells,
          Makers.COURSE_NAME_MAKER);
      for (final String courseName : res) {
        cells2[0] = new Cell(Tables.getTable("courses").getColumn("courseName"), courseName);
        final Course courseData = DBHandler.selectElement(cm.getConn(), "courses", cells2, courseMaker);
        $.add(courseData);
      }
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return $;
  }
  
  // included in available queries - no need for transaction
  public static Collection<String> getMyCoursesNames(final Connection c, final String username)
      throws ElementNotFoundException {
    final Collection<String> $ = new LinkedList<String>();
    final Cell[] cells = DBHandler.singleParam(new Cell(Tables.getTable("registrations")
        .getColumn("username"), username));
    final Cell[] cells2 = DBHandler.singleParam(null);
    final Iterable<String> res = DBHandler.selectSome(c, "courseName", "registrations", cells,
        Makers.COURSE_NAME_MAKER);
    for (final String courseName : res) {
      cells2[0] = new Cell(Tables.getTable("courses").getColumn("courseName"), courseName);
      final String courseData = DBHandler.selectElement(c, "courseName", "courses", cells2,
          Makers.COURSE_NAME_MAKER);
      $.add(courseData);
    }
    return $;
  }
  
  public static void removeCourseFromUser(final String username, final String courseName)
      throws NumberFormatException {
    final Cell[] cells = new Cell[2];
    cells[0] = new Cell(Tables.getTable("registrations").getColumn("username"), username);
    cells[1] = new Cell(Tables.getTable("registrations").getColumn("courseName"), courseName);
    final ConnectionManager cm = cp.captureConnection();
    try {
      DBHandler.delete(cm.getConn(), "registrations", cells);
      updateCourseSpace(cm.getConn(), courseName, 1);
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static void updateUserField(final String username, final String field, final String newFieldValue) {
    final Cell[] cells = new Cell[2];
    cells[0] = new Cell(Tables.getTable("users").getColumn(field), newFieldValue);
    cells[1] = new Cell(Tables.getTable("users").getColumn("username"), username);
    final ConnectionManager cm = cp.captureConnection();
    try {
      DBHandler.update(cm.getConn(), "users", cells);
      cm.getConn().commit();
    } catch (final SQLException e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  // included in removeFromUser / addCourseToUser
  private static void updateCourseSpace(final Connection c, final String courseName, final int addition)
      throws ElementNotFoundException {
    final Cell[] cells = DBHandler.singleParam(new Cell(
        Tables.getTable("capacities").getColumn("courseName"), courseName));
    final int courseCapacity = Integer.parseInt(DBHandler.selectElement(c, "space", "capacities", cells,
        new Maker<String>() {
          @Override public String make(final ResultSet r) throws SQLException {
            return r.getString("space");
          }
        }));
    final Cell[] cells3 = new Cell[2];
    cells3[0] = new Cell(Tables.getTable("capacities").getColumn("space"), Integer.valueOf(courseCapacity
        + addition));
    cells3[1] = new Cell(Tables.getTable("capacities").getColumn("courseName"), courseName);
    DBHandler.update(c, "capacities", cells3);
  }
  
  public static void addCourseToUser(final String username, final String courseName, final int groupNum) {
    final Cell[] cells = new Cell[3];
    cells[0] = new Cell(Tables.getTable("registrations").getColumn("username"), username);
    cells[1] = new Cell(Tables.getTable("registrations").getColumn("courseName"), courseName);
    cells[2] = new Cell(Tables.getTable("registrations").getColumn("groupNum"), Integer.valueOf(groupNum));
    final ConnectionManager cm = cp.captureConnection();
    try {
      DBHandler.insert(cm.getConn(), "registrations", cells);
      updateCourseSpace(cm.getConn(), courseName, -1);
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  static Iterable<Session> getLectures(final String courseId) {
    return getSessions(courseId, Boolean.TRUE);
  }
  
  static Iterable<Session> getTutorials(final String courseId) {
    return getSessions(courseId, Boolean.FALSE);
  }
  
  private static Iterable<Session> getSessions(final String courseId, final Boolean isLecture) {
    final Table sessions = Tables.getTable("sessions");
    final ConnectionManager cm = cp.captureConnection();
    try {
      return DBHandler.selectSome(
          cm.getConn(),
          "sessions",
          new Cell[] { new Cell(sessions.getColumn("courseId"), courseId),
              new Cell(sessions.getColumn("isLecture"), isLecture) }, Makers.SESSION_MAKER);
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  private static Collection<Session> getCourseSessions(final String courseId) {
    final Table sessions = Tables.getTable("sessions");
    final ConnectionManager cm = cp.captureConnection();
    try {
      return DBHandler.selectSome(cm.getConn(), "sessions",
          DBHandler.singleParam(new Cell(sessions.getColumn("courseId"), courseId)), Makers.SESSION_MAKER);
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static Iterable<Session> getAllUserCoursesSessions(final Iterable<Course> myCourses) {
    final Table sessions = Tables.getTable("sessions");
    final Collection<Session> $ = new LinkedList<Session>();
    if (!myCourses.iterator().hasNext())
      // no courses means no sessions
      return $;
    final ConnectionManager cm = cp.captureConnection();
    try {
      for (final Course c : myCourses) {
        final Collection<Session> session = DBHandler.selectSome(cm.getConn(), "sessions",
            new Cell[] { new Cell(sessions.getColumn("courseId"), c.courseId) }, Makers.SESSION_MAKER);
        $.addAll(session);
      }
      cm.getConn().commit();
    } catch (final Exception e) {
      try {
        cm.getConn().rollback();
      } catch (final SQLException e1) {
        logger.error("rollback failed", e1);
      }
    } finally {
      cp.releaseConnection(cm);
    }
    return $;
  }
  
  public static int sessionsToAddAreValid(final ArrayList<Session> sessions, final int groupNum) {
    for (int i = 0; i < sessions.size(); i++)
      for (int j = i + 1; j < sessions.size(); j++) {
        final ArrayList<Session> sessionOne = new ArrayList<Session>();
        final ArrayList<Session> sessionTwo = new ArrayList<Session>();
        sessionOne.add(sessions.get(i));
        sessionTwo.add(sessions.get(j));
        if (overlaps(sessionOne, sessionTwo)) // overlap among course sessions
          return 1;
      }
    final Iterable<Course> allCourses = ORM.getAllCourses();
    final Iterator<Course> cIter = allCourses.iterator();
    while (cIter.hasNext()) {
      final Course c = cIter.next();
      if (c.groupNum == groupNum)
        if (overlaps(c.lectures, sessions) || overlaps(c.tutorials, sessions))
          return 2;
    }
    return 0;
  }
  
  public static Iterable<Session> getAllSessions() {
    final ConnectionManager cm = cp.captureConnection();
    try {
      return DBHandler.selectSome(cm.getConn(), "sessions", DBHandler.NO_PARAMS, Makers.SESSION_MAKER);
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static void addCourseSession(final Connection c, final Session s) {
    DBHandler.insert(
        c,
        "sessions",
        Tables.getTable("sessions").makeCellArray(s.courseId, Integer.valueOf(s.day.ordinal()),
            Integer.valueOf(s.startHour), Integer.valueOf(s.duration), s.teacherName, s.classLocation,
            Boolean.valueOf(s.isLecture)));
  }
  
  public static Iterable<User> getAllUsers() {
    final ConnectionManager cm = cp.captureConnection();
    try {
      return DBHandler.selectSome(cm.getConn(), "users", DBHandler.NO_PARAMS, Makers.USER_MAKER);
    } finally {
      cp.releaseConnection(cm);
    }
  }
  
  public static Collection<Integer> getUserStudyGroups(final Connection c, final String username) {
    final Cell[] cells = new Cell[1];
    cells[0] = new Cell(Tables.getTable("registrations").getColumn("username"), username);
    return DBHandler.selectDistinct(c, "groupNum", "registrations", cells, new Maker<Integer>() {
      @Override public Integer make(final ResultSet r) throws SQLException {
        return Integer.valueOf(r.getInt("groupNum"));
      }
    });
  }
}
